﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using PMS.Exceptions;
using PMS.Utils;

namespace PMS.DAL
{
    public class SQLDatabase:SQLObjectHelper
    {
        static ConfigEngine cf = new ConfigEngine();
        private static string ConnectionString = cf.Connectingstring(cf.ReadConnectingString());
        protected static SqlConnection DBConnection = new SqlConnection(ConnectionString);
        private SqlCommand command;
        private DbProviderFactory dbProviderFactory;

        protected static DataSet ExecuteToDataset(SqlCommand CM)
        {
            DataSet ds = new DataSet();
            try
            {
                CM.Connection = DBConnection;
                DBConnection.Open();
                SqlDataAdapter adap = new SqlDataAdapter(CM);
                adap.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw DataAccessException.GetException("Data access error:", ex);
            }
            finally
            {
                DBConnection.Close();
            }

        }

        protected static T ExecuteQuery<T>(SqlCommand CM)
        {
            T newObjectToReturn = Activator.CreateInstance<T>();
            newObjectToReturn = default(T);
            try
            {
                CM.Connection = DBConnection;
                DBConnection.Open();
                SqlDataReader reader = CM.ExecuteReader();
                if (reader.Read())
                {
                    newObjectToReturn = GetAs<T>(reader);
                }
            }
            catch (Exception ex)
            {
                throw DataAccessException.GetException("Data access error:", ex);
            }
            finally
            {
                DBConnection.Close();
            }
            return newObjectToReturn;

        }

        protected static List<T> GetListFromReader<T>(SqlDataReader reader)
        {
            List<T> ListItems = new List<T>();
            while (reader.Read())
            {
                T newObjectToReturn = Activator.CreateInstance<T>();
                newObjectToReturn = GetAs<T>(reader);
                ListItems.Add(newObjectToReturn);
            }

            return ListItems;
        }

        protected static SqlDataReader ExecuteReader(SqlCommand CM)
        {
            SqlDataReader reader;
            try
            {
                CM.Connection = DBConnection;
                DBConnection.Open();
                reader = CM.ExecuteReader();

            }
            catch (Exception ex)
            {
                throw DataAccessException.GetException("Data access error:", ex);
            }
            finally
            {
                DBConnection.Close();
            }
            return reader;
        }

        protected static int ExecuteNonQuery(SqlCommand CM)
        {
            int roweffect = 0;
            try
            { 
                DBConnection.Open();
                CM.Connection = DBConnection;
                roweffect = CM.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw DataAccessException.GetException("Data access error:", ex);
                //result = false;
            }
            finally
            {
                DBConnection.Close();
            }
            return roweffect;
        }

        protected static List<T> ExecuteQuery<T>(SqlCommand CM, bool isList)
        {
            List<T> ListItems = new List<T>();
            try
            {
                CM.Connection = DBConnection;
                DBConnection.Open();
                SqlDataReader reader = CM.ExecuteReader();
                while (reader.Read())
                {
                    T newObjectToReturn = Activator.CreateInstance<T>();
                    newObjectToReturn = GetAs<T>(reader);
                    ListItems.Add(newObjectToReturn);
                }
            }
            catch (Exception ex)
            {
                throw DataAccessException.GetException("Data access error:", ex);
            }
            finally
            {
                DBConnection.Close();
            }
            return ListItems; ;
        }

        protected static T GetFromReader<T>(SqlDataReader reader)
        {
          
            if (reader.Read())
            {
                T newObjectToReturn = Activator.CreateInstance<T>();
                return newObjectToReturn = GetAs<T>(reader);
            }
            return Activator.CreateInstance<T>();
        }
        
        protected static SqlCommand StoredProcedureCommand(string storename)
        {
           
                SqlCommand cm = new SqlCommand(storename);
                cm.CommandType = CommandType.StoredProcedure;
                cm.Connection = DBConnection;
                return cm;
 
        }

        protected static void AddInParameter(SqlCommand command, string name, SqlDbType SqlDbType, object value)
        {
            DbParameter para = command.Parameters.Add(name,SqlDbType);
            para.Direction = ParameterDirection.Input;
            para.Value = (value == null) ? DBNull.Value : value;
        }

        protected static void AddOutParameter(SqlCommand command, string name, SqlDbType SqlDbType, object value)
        {
            DbParameter para = command.Parameters.Add(name,SqlDbType,250);
            para.Direction = ParameterDirection.Output;
            para.Value = (value == null) ? DBNull.Value : value;
        }

        /// <summary>
        /// Gets a parameter value.
        /// </summary>
        /// <param name="command">The command that contains the parameter.</param>
        /// <param name="name">The name of the parameter.</param>
        /// <returns>The value of the parameter.</returns>
        protected virtual object GetParameterValue(DbCommand command, string name)
        {
            return command.Parameters[name].Value;
        }
    }

}
